USE [RD_COMMONWORK]
GO
/****** Object:  StoredProcedure [dbo].[USP_Update_BusinessUnitsecurity_ACG]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Update_BusinessUnitsecurity_ACG]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create procedure [dbo].[USP_Update_BusinessUnitsecurity_ACG]

as

truncate table JDEBOBusinessUnitSecurity


insert into JDEBOBusinessUnitSecurity
values (''rryff'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''jmellides'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''htrantham'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''sduffy'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''jbennett'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''administrator'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''jguerra'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''bmoffatt'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''dbouchard'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''mtopel'','' '',''            '',''ZZZZZZZZZZZZ'')
insert into JDEBOBusinessUnitSecurity
values (''jstange'','' '',''            '',''ZZZZZZZZZZZZ'')

select * from JDEBOBusinessUnitSecurity
' 
END
GO
/****** Object:  Table [dbo].[ITEM_SUPPLEMENTAL]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ITEM_SUPPLEMENTAL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ITEM_SUPPLEMENTAL](
	[ITEMNUMBERID] [varchar](20) NULL,
	[SOURCEID] [int] NOT NULL,
	[SOURCE] [varchar](20) NULL,
	[ITEMNUMBER] [int] NOT NULL,
	[L1_FINISHED_GOODS_CODE] [varchar](3) NULL,
	[L1_FINISHED_GOODS] [varchar](30) NULL,
	[L2_FINISHED_GOODS_CODE] [varchar](3) NULL,
	[L2_FINISHED_GOODS] [varchar](30) NULL,
	[L3_FINISHED_GOODS_CODE] [varchar](3) NULL,
	[L3_FINISHED_GOODS] [varchar](30) NULL,
	[L4_FINISHED_GOODS_CODE] [varchar](3) NULL,
	[L4_FINISHED_GOODS] [varchar](30) NULL,
	[BRANDLEVELCODE] [varchar](3) NULL,
	[BRANDLEVEL] [varchar](30) NULL,
	[BEANTYPECODE] [varchar](3) NULL,
	[BEANTYPE] [varchar](30) NULL,
	[BRANDNAMECODE] [varchar](3) NULL,
	[BRANDNAME] [varchar](30) NULL,
	[COFFEETYPECODE] [varchar](3) NULL,
	[COFFEETYPE] [varchar](30) NULL,
	[VARIETYCODE] [varchar](3) NULL,
	[VARIETY] [varchar](30) NULL,
	[TIERCODE] [varchar](3) NULL,
	[TIER] [varchar](30) NULL,
	[FILTERCODE] [varchar](3) NULL,
	[FILTER] [varchar](30) NULL,
	[CCP_ONLY_CODE] [varchar](3) NULL,
	[CCP_ONLY] [varchar](30) NULL,
	[CERT_SUB_CODE] [varchar](3) NULL,
	[CERT_SUB] [varchar](30) NULL,
	[SEASONALCODE] [varchar](3) NULL,
	[SEASONAL] [varchar](30) NULL,
	[INSERTINDICATOR] [int] NULL,
	[INSERTDATE] [datetime] NULL,
	[CHANGEINDICATOR] [int] NULL,
	[CHANGEDATE] [datetime] NULL,
	[DELETEINDICATOR] [int] NULL,
	[DELETEDATE] [datetime] NULL,
	[PURGEDATE] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[SOURCEID] ASC,
	[ITEMNUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[JDE_ITEM_SUPPL]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JDE_ITEM_SUPPL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JDE_ITEM_SUPPL](
	[ITEMID] [varchar](20) NULL,
	[SOURCEID] [int] NOT NULL,
	[SOURCE] [varchar](20) NULL,
	[ITEMNUMBER] [int] NOT NULL,
	[SUPPLEMENTALDATABASECODE] [varchar](4) NOT NULL,
	[TYPEDATA] [varchar](2) NOT NULL,
	[EFFECTIVEDATE] [datetime] NULL,
	[UDCVALUES] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
	[SOURCEID] ASC,
	[ITEMNUMBER] ASC,
	[SUPPLEMENTALDATABASECODE] ASC,
	[TYPEDATA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[JDEABAddressBookOrgStructure]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JDEABAddressBookOrgStructure]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JDEABAddressBookOrgStructure](
	[SourceID] [int] NOT NULL,
	[Source] [nchar](20) NOT NULL,
	[StructureType] [nchar](3) NOT NULL,
	[ParentNumber] [int] NOT NULL,
	[AddressNumber] [int] NOT NULL,
	[DisplaySequence] [decimal](20, 7) NOT NULL,
	[BeginningEffectiveDate] [datetime] NOT NULL,
	[EndingEffectiveDate] [datetime] NOT NULL,
	[Remark] [nchar](30) NOT NULL,
	[UserID] [nchar](10) NOT NULL,
	[DateUpdated] [datetime] NOT NULL,
	[ProgramID] [nchar](10) NOT NULL,
	[WorkStationID] [nchar](10) NOT NULL,
	[LastTimeUpdated] [int] NOT NULL,
	[InsertIndicator] [int] NOT NULL,
	[InsertDate] [datetime] NOT NULL,
	[ChangeIndicator] [int] NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[DeleteIndicator] [int] NOT NULL,
	[DeleteDate] [datetime] NOT NULL,
	[PurgeDate] [datetime] NOT NULL,
 CONSTRAINT [PK_JDEABAddressBookOrgStructure] PRIMARY KEY CLUSTERED 
(
	[StructureType] ASC,
	[ParentNumber] ASC,
	[AddressNumber] ASC,
	[SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[JDEBOBusinessUnitSecurity]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JDEBOBusinessUnitSecurity]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JDEBOBusinessUnitSecurity](
	[BOUserID] [char](30) NOT NULL,
	[JDEUserID] [char](30) NOT NULL,
	[FromRole] [char](20) NULL,
	[BUFrom] [char](12) NOT NULL,
	[BUTo] [char](12) NOT NULL,
 CONSTRAINT [PK_JDEBOBusinessUnitSecurity] PRIMARY KEY CLUSTERED 
(
	[BOUserID] ASC,
	[BUFrom] ASC,
	[BUTo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[JDEGSUserDefinedCodes]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JDEGSUserDefinedCodes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JDEGSUserDefinedCodes](
	[SourceID] [int] NOT NULL,
	[Source] [nchar](20) NOT NULL,
	[InstallSystemCode] [nchar](4) NOT NULL,
	[UserDefinedCode] [nchar](2) NOT NULL,
	[UserDefinedCodesValues] [nchar](10) NOT NULL,
	[Description01] [nchar](30) NOT NULL,
	[Description02] [nchar](30) NOT NULL,
	[SpecialHandlingCode] [nchar](10) NOT NULL,
	[OwnershipFlag] [nchar](1) NOT NULL,
	[HardCodedYN] [nchar](1) NOT NULL,
	[UserID] [nchar](10) NOT NULL,
	[ProgramID] [nchar](10) NOT NULL,
	[DateUpdated] [datetime] NOT NULL,
	[WorkStationID] [nchar](10) NOT NULL,
	[LastTimeUpdated] [int] NOT NULL,
	[InsertIndicator] [int] NOT NULL,
	[InsertDate] [datetime] NOT NULL,
	[ChangeIndicator] [int] NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[DeleteIndicator] [int] NOT NULL,
	[DeleteDate] [datetime] NOT NULL,
	[PurgeDate] [datetime] NOT NULL,
 CONSTRAINT [PK_JDEGSUserDefinedCodes] PRIMARY KEY CLUSTERED 
(
	[InstallSystemCode] ASC,
	[UserDefinedCode] ASC,
	[UserDefinedCodesValues] ASC,
	[SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[JDEGSUserDefinedCodeTypes]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JDEGSUserDefinedCodeTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JDEGSUserDefinedCodeTypes](
	[SourceID] [int] NOT NULL,
	[Source] [nchar](20) NOT NULL,
	[InstallSystemCode] [nchar](4) NOT NULL,
	[UserDefinedCode] [nchar](2) NOT NULL,
	[Description01] [nchar](30) NOT NULL,
	[SequenceNumber] [decimal](20, 7) NOT NULL,
	[ClassCode] [nchar](3) NOT NULL,
	[CodeLength] [int] NOT NULL,
	[Line2DesiredYN] [nchar](1) NOT NULL,
	[NumericYN] [nchar](1) NOT NULL,
	[JDEMergeControl] [nchar](1) NOT NULL,
	[MergeType] [nchar](2) NOT NULL,
	[UserID] [nchar](10) NOT NULL,
	[ProgramID] [nchar](10) NOT NULL,
	[DateUpdated] [datetime] NOT NULL,
	[WorkStationID] [nchar](10) NOT NULL,
	[LastTimeUpdated] [int] NOT NULL,
	[InsertIndicator] [int] NOT NULL,
	[InsertDate] [datetime] NOT NULL,
	[ChangeIndicator] [int] NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[DeleteIndicator] [int] NOT NULL,
	[DeleteDate] [datetime] NOT NULL,
	[PurgeDate] [datetime] NOT NULL,
 CONSTRAINT [PK_JDEGSUserDefinedCodeTypes] PRIMARY KEY CLUSTERED 
(
	[InstallSystemCode] ASC,
	[UserDefinedCode] ASC,
	[SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[JOIN_CUSTOMER_SUPPL]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JOIN_CUSTOMER_SUPPL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JOIN_CUSTOMER_SUPPL](
	[CUSTOMERID] [bigint] NOT NULL,
	[SOURCEID] [int] NOT NULL,
	[SOURCE] [varchar](20) NOT NULL,
	[CUSTOMERNUMBER] [int] NOT NULL,
	[SUPPLEMENTALDATABASECODE] [varchar](4) NOT NULL,
	[TYPEDATA] [varchar](2) NOT NULL,
	[REMARK] [varchar](30) NOT NULL,
	[NAMEREMARK] [varchar](30) NOT NULL,
	[TEXTREMARK] [varchar](50) NOT NULL,
	[UDCVALUES] [varchar](10) NOT NULL,
 CONSTRAINT [PK__JOIN_CUSTOMER_SU__3F466844] PRIMARY KEY CLUSTERED 
(
	[SOURCEID] ASC,
	[CUSTOMERNUMBER] ASC,
	[SUPPLEMENTALDATABASECODE] ASC,
	[TYPEDATA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[STAR_ITEM_SUPPL_TEMP]    Script Date: 5/30/13 12:59:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STAR_ITEM_SUPPL_TEMP]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[STAR_ITEM_SUPPL_TEMP](
	[SOURCEID] [int] NOT NULL,
	[SOURCE] [varchar](20) NULL,
	[ITEMNUMBER] [int] NOT NULL,
	[BRANDLEVEL_TYPEDATA] [varchar](2) NULL,
	[BRANDLEVEL_UDCVALUES] [varchar](10) NULL,
	[BEANTYPE_TYPEDATA] [varchar](2) NULL,
	[BEANTYPE_UDCVALUES] [varchar](10) NULL,
	[BRANDNAME_TYPEDATA] [varchar](2) NULL,
	[BRANDNAME_UDCVALUES] [varchar](10) NULL,
	[COFFEETYPE_TYPEDATA] [varchar](2) NULL,
	[COFFEETYPE_UDCVALUES] [varchar](10) NULL,
	[VARIETY_TYPEDATA] [varchar](2) NULL,
	[VARIETY_UDCVALUES] [varchar](10) NULL,
	[TIER_TYPEDATA] [varchar](2) NULL,
	[TIER_UDCVALUES] [varchar](10) NULL,
	[FILTER_TYPEDATA] [varchar](2) NULL,
	[FILTER_UDCVALUES] [varchar](10) NULL,
	[CCP_ONLY_TYPEDATA] [varchar](2) NULL,
	[CCP_ONLY_UDCVALUES] [varchar](10) NULL,
	[CERT_SUB_TYPEDATA] [varchar](2) NULL,
	[CERT_SUB_UDCVALUES] [varchar](10) NULL,
	[SEASONAL_TYPEDATA] [varchar](2) NULL,
	[SEASONAL_UDCVALUES] [varchar](10) NULL,
	[L1_FINISHED_GOODS_TYPEDATA] [varchar](2) NULL,
	[L1_FINISHED_GOODS_UDCVALUES] [varchar](10) NULL,
	[L2_FINISHED_GOODS_TYPEDATA] [varchar](2) NULL,
	[L2_FINISHED_GOODS_UDCVALUES] [varchar](10) NULL,
	[L3_FINISHED_GOODS_TYPEDATA] [varchar](2) NULL,
	[L3_FINISHED_GOODS_UDCVALUES] [varchar](10) NULL,
	[L4_FINISHED_GOODS_TYPEDATA] [varchar](2) NULL,
	[L4_FINISHED_GOODS_UDCVALUES] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
	[SOURCEID] ASC,
	[ITEMNUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Index [_dta_index_JOIN_CUSTOMER_SUPPL_13_1077578877__K2_K4]    Script Date: 5/30/13 12:59:37 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[JOIN_CUSTOMER_SUPPL]') AND name = N'_dta_index_JOIN_CUSTOMER_SUPPL_13_1077578877__K2_K4')
CREATE NONCLUSTERED INDEX [_dta_index_JOIN_CUSTOMER_SUPPL_13_1077578877__K2_K4] ON [dbo].[JOIN_CUSTOMER_SUPPL]
(
	[SOURCEID] ASC,
	[CUSTOMERNUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
